[小ネタ]Snowflakeの名前付きステージのファイルフォーマットをCOPY時に上書き指定できるか試してみた

「カスタマーストーリー」を支えるツール特集

#ビッグデータ

#ビジネス・アナリティクス

#ビジネス

#Snowflake

大高大輔

2020.07.20

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは！DA(データアナリティクス)事業本部インテグレーション部の大高です。

Snowflakeの「内部ステージ」における「名前付きステージ」では、ステージの作成時にファイルフォーマットを指定するのでCOPYコマンド実行時にはファイルフォーマットを指定する必要はありません。

一方で「COPYコマンド実行時に明示的に指定したらどうなるのか？」というのが気になったので試してみました。

なお「名前付きステージ」を利用したCOPYについては以下の記事でも触れていますので、よろしければご参照ください。

前提条件

以下のような「ヘッダ行付き」、「カンマ区切り」のCSVファイルをローカルPCに準備しておきます。

id,name,age
1,Aruto Hiden,22
2,Izu,
3,Isamu Fuwa,27
4,Yua Yaiba,24
5,Gai Amatsu,45
6,Horobi,
7,Naki,
8,Jin,
9,Ikazuchi,

また、Snowflakeには事前にデータベースとテーブルを用意しておきます。

USE ROLE OOTAKA_SANDBOX_ROLE; -- ROLEは事前作成済みのものを利用します
CREATE DATABASE OOTAKA_SANDBOX_DB;

USE DATABASE OOTAKA_SANDBOX_DB;
CREATE TABLE public.users(
  id INTEGER,
  name STRING,
  age INTEGER
);

なお、Snowflakeの操作については、すべてSnowSQLで実行します。

検証準備

ステージの作成

まずは、名前付きステージを作成します。ここでは用意したCSVファイルのCOPY時にあえてエラーになるように、以下のような「ヘッダ行無し」、「タブ区切り」ファイルのフォーマットを指定しておきます。

file_format = (type = csv field_delimiter = '\t' skip_header = 0)

では作ってみます。

$ snowsql
* SnowSQL * v1.2.7
Type SQL statements or !help
foo_bar#(no warehouse)@(no database).(no schema)>USE DATABASE OOTAKA_SANDBOX_DB;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.140s
foo_bar#(no warehouse)@OOTAKA_SANDBOX_DB.PUBLIC>create or replace stage zero_one_stage file_format = (type = csv field_delimiter = '\t' skip_header = 0);
+-------------------------------------------------+
| status                                          |
|-------------------------------------------------|
| Stage area ZERO_ONE_STAGE successfully created. |
+-------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.204s
foo_bar#(no warehouse)@(no database).(no schema)>!q
Goodbye!

COPY時にエラーになるであろうステージが作成できました。

ファイルのステージング

では、準備ができたのでまずはファイルをステージング（アップロード）します。ステージングするファイルを改めて確認します。

$ cat /tmp/users.csv
id,name,age
1,Aruto Hiden,22
2,Izu,
3,Isamu Fuwa,27
4,Yua Yaiba,24
5,Gai Amatsu,45
6,Horobi,
7,Naki,
8,Jin,
9,Ikazuchi,

「ヘッダ行付き」、「カンマ区切り」のCSVファイルで、問題ないですね。これをステージングします。

$ snowsql
* SnowSQL * v1.2.7
Type SQL statements or !help
foo_bar#(no warehouse)@(no database).(no schema)>USE DATABASE OOTAKA_SANDBOX_DB;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.123s
foo_bar#(no warehouse)@OOTAKA_SANDBOX_DB.PUBLIC>put file:///tmp/users.csv @zero_one_stage;
users.csv_c.gz(0.00MB): [##########] 100.00% Done (0.075s, 0.00MB/s).
+-----------+--------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source    | target       | source_size | target_size | source_compression | target_compression | status   | message |
|-----------+--------------+-------------+-------------+--------------------+--------------------+----------+---------|
| users.csv | users.csv.gz |         130 |         147 | NONE               | GZIP               | UPLOADED |         |
+-----------+--------------+-------------+-------------+--------------------+--------------------+----------+---------+
1 Row(s) produced. Time Elapsed: 2.690s

ステージングされました。これで準備完了です。

検証してみる

エラーが起きるかの確認

まずは、単純にCOPYコマンドを実行して、ファイルフォーマットエラーが起きるか確認しておきます。

ウェアハウス指定が必要なので最初にUSE WAREHOUSEをします。

foo_bar#(no warehouse)@OOTAKA_SANDBOX_DB.PUBLIC>USE WAREHOUSE X_SMALL_WH;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.563s

では、COPYしてみます。

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>copy into users from @zero_one_stage;
100080 (22000): Number of columns in file (1) does not match that of the corresponding table (3), use file format option error_on_column_count_mismatch=false to ignore this error
  File 'users.csv.gz', line 2, character 1
  Row 1 starts at line 1, column "USERS"["ID":1]
  If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.

想定通り、エラーになりましたね。

ファイルフォーマットを上書き指定してCOPYしてみる

それでは、本題です。ファイルフォーマットを上書き指定してCOPYしてみましょう。正しいフォーマットは以下になります。

file_format = (type = csv field_delimiter = ',' skip_header = 1)

これを指定してCOPYコマンドを実行してみます。

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>copy into users from @zero_one_stage file_format = (type = csv field_delimiter = ',' skip_header = 1);
+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                        | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| zero_one_stage/users.csv.gz | LOADED |           9 |           9 |           1 |           0 | NULL        |             NULL |                  NULL | NULL                    |
+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
1 Row(s) produced. Time Elapsed: 2.796s

成功しました！ちゃんとCOPYコマンド実行時に指定したものが優先されるようですね。一応、テーブルの中身も確認しておきます。

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>SELECT * FROM USERS;
+----+-------------+-----+
| ID | NAME        | AGE |
|----+-------------+-----|
|  1 | Aruto Hiden |  22 |
|  2 | Izu         | NULL |
|  3 | Isamu Fuwa  |  27 |
|  4 | Yua Yaiba   |  24 |
|  5 | Gai Amatsu  |  45 |
|  6 | Horobi      | NULL |
|  7 | Naki        | NULL |
|  8 | Jin         | NULL |
|  9 | Ikazuchi    | NULL |
+----+-------------+-----+
9 Row(s) produced. Time Elapsed: 1.191s

問題ないですね。

後片付け

最後に、名前付きステージの削除をして、ステージングしたファイルごと削除しておきます。

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>drop stage zero_one_stage;
+--------------------------------------+
| status                               |
|--------------------------------------|
| ZERO_ONE_STAGE successfully dropped. |
+--------------------------------------+
1 Row(s) produced. Time Elapsed: 0.594s